package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Village;
import com.xqgl.dao.VillageDao;
import com.xqgl.util.ConnertionUtil;

public class VillageDaoImp implements VillageDao{
	
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	@Override
	public Village selectVillage(String villagename) {
		Village v = new Village();
		String sql = "select * from village where villagename = ?";
		try{
			ps = conn.prepareStatement(sql);
			ps.setString(1, villagename);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				v.setId(set.getInt("id"));
				v.setVillagenum(set.getString("villagenum"));
				v.setVillagename(set.getString("villagename"));
				v.setVillageaddress(set.getString("villageaddress"));
				v.setVillagesize(set.getDouble("villagesize"));
				v.setVillagefloor(set.getInt("villagefloor"));
				v.setVillagehousehold(set.getInt("villagehousehold"));
				v.setVillagegreen(set.getInt("villagegreen"));
				v.setVillagethum(set.getString("villagethum"));
				v.setDevelopername(set.getString("developername"));
				v.setPropertyname(set.getString("propertyname"));
				v.setVillagectime(set.getString("villagectime"));
				v.setVillagestate(set.getString("villagestate"));
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally{
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return v;
	}

	@Override
	public int add(Village village) throws SQLException {
		String sql = "insert into village(villagenum,villagename,villageaddress,villagesize,villagefloor,villagehousehold,villagegreen,villagethum,developername,propertyname,villagectime,villagestate)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, village.getVillagenum());
		ps.setString(2, village.getVillagename());
		ps.setString(3, village.getVillageaddress());
		ps.setDouble(4, village.getVillagesize());
		ps.setInt(5, village.getVillagefloor());
		ps.setInt(6, village.getVillagehousehold());
		ps.setInt(7, village.getVillagegreen());
		ps.setString(8, village.getVillagethum());
		ps.setString(9, village.getDevelopername());
		ps.setString(10, village.getPropertyname());
		ps.setString(11, village.getVillagectime());
		ps.setString(12, village.getVillagestate());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}
	
	public ArrayList<Village> getAllUserInfos() {
		ArrayList<Village> villages = new ArrayList<>();
		String sql = "select * from village ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst = ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Village village = new Village();
				village.setId(rst.getInt("id"));
				village.setVillagenum(rst.getString("villagenum"));
				village.setVillagename(rst.getString("villagename"));
				village.setVillageaddress(rst.getString("villageaddress"));
				village.setVillagesize(rst.getDouble("villagesize"));
				village.setVillagefloor(rst.getInt("villagefloor"));
				village.setVillagehousehold(rst.getInt("villagehousehold"));
				village.setVillagegreen(rst.getInt("villagegreen"));
				village.setVillagethum(rst.getString("villagethum"));
				village.setDevelopername(rst.getString("developername"));
				village.setPropertyname(rst.getString("propertyname"));
				village.setVillagectime(rst.getString("villagectime"));
				village.setVillagestate(rst.getString("villagestate"));
				villages.add(village);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return villages;
	}
	
	public int update(Village village) throws SQLException{
		String sql = "update village set villagenum = '"+village.getVillagenum()+"', villagename = '"+village.getVillagename()+"', villageaddress = '"+village.getVillageaddress()+"', "
				+ "villagesize = "+village.getVillagesize()+", villagefloor = "+village.getVillagefloor()+", villagehousehold = "+village.getVillagehousehold()+", "
						+ "villagegreen = "+village.getVillagegreen()+", villagethum = '"+village.getVillagethum()+"', developername = '"+village.getDevelopername()+"', "
								+ "propertyname = '"+village.getPropertyname()+"', villagectime = '"+village.getVillagectime()+"', villagestate = '"+village.getVillagestate()+"' where id='"+village.getId()+"'";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}
	
	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from village where id = '"+id+"' ";
		System.out.println(sql);
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Village findById(int id) throws SQLException {
		Village village = new Village();
		String sql = "select * from village where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				village.setId(set.getInt("id"));
				village.setVillagenum(set.getString("villagenum"));
				village.setVillagename(set.getString("villagename"));
				village.setVillageaddress(set.getString("villageaddress"));
				village.setVillagesize(set.getDouble("villagesize"));
				village.setVillagefloor(set.getInt("villagefloor"));
				village.setVillagehousehold(set.getInt("villagehousehold"));
				village.setVillagegreen(set.getInt("villagegreen"));
				village.setVillagethum(set.getString("villagethum"));
				village.setDevelopername(set.getString("developername"));
				village.setPropertyname(set.getString("propertyname"));
				village.setVillagectime(set.getString("villagectime"));	
				village.setVillagestate(set.getString("villagestate"));
				
			}
			ps.close();
			return village;
	}
}
